# Copyright (c) 2022, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe import _


def execute(filters=None):
	# get_data要传参filters
	return get_columns(filters), get_data(filters)


def get_columns(filters):
	columns = [
		{
			"label": _("Operate Time"),
			"fieldtype": "Date",
			"fieldname": "creation",
			"width": 150,
		},
		{
			"label": _("DocType"),
			"fieldtype": "Data",
			"fieldname": "document_type",
			"width": 200,
		},
		{
			"label": _("Billno"),
			"fieldtype": "Data",
			"fieldname": "reference_name",
			"width": 200,
		},
		{
			"label": _("State From"),
			"fieldtype": "Date",
			"fieldname": "state",
			"width": 120,
		},
		{
			"label": _("Action"),
			"fieldtype": "Data",
			"fieldname": "action",
			"width": 120,
		},
		{
			"label": _("State Next"),
			"fieldtype": "Data",
			"fieldname": "content",
			"width": 120,
		},
		{
			"label": _("User"),
			"fieldtype": "Data",
			"fieldname": "comment_email",
			"width": 260,
		},
	]

	return columns


def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	doctype, creation_from, creation_to = filters.get('doctype'), filters.get('creation_from'), filters.get('creation_to')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	if doctype:
		conditions += f" AND wo.document_type = '{doctype}' "
	# 把conditions组合进来
	dt = frappe.db.sql(
		f"""SELECT co.creation, wo.document_type, co.reference_name, wo.state, wo.action, co.content, 
			co.comment_email 
			from `tabComment` co, 
			(SELECT wp.document_type, wc.state, wc.action, wc.next_state FROM `tabWorkflow` wp JOIN `tabWorkflow Transition` wc ON wp.name = wc.parent) wo 
			WHERE wo.document_type = co.reference_doctype AND wo.next_state = co.content
			AND co.comment_type = 'Workflow'
			AND co.creation >= '{creation_from}' and co.creation <= '{creation_to}' {conditions}
			ORDER BY co.creation DESC;
		""", as_dict=True
	)

	return dt